package com.smartwebx.ha.dal.realtime.sqlprovider;

import java.util.Date;
import java.util.Map;

import org.apache.commons.lang3.time.DateUtils;

import com.smartwebx.ha.dal.config.OracleConfigInfo;

import static org.apache.ibatis.jdbc.SqlBuilder.*;

public class GrwProvider {
	private String ST_GRW_R = OracleConfigInfo.REALTIME_NAME + ".ST_GRW_R";
	private String ST_STBPRP_B = OracleConfigInfo.REALTIME_NAME
			+ ".ST_STBPRP_B";

	public String getAllStatGroundWaterSQL(Map<String, Date> maps) {
		maps = this.reParams(maps);
		
		BEGIN();
		
		String primaryTableSql1 = primaryTableBuild1();
		String primaryTableSql2 = primaryTableBuild2();
		String slaveTableSql = slaveTableBuild();
		SELECT(columnToFieldMap());
		FROM("(" + primaryTableSql1 + ") T1");
		LEFT_OUTER_JOIN("(" +primaryTableSql2 + " DESC) T2 on T1.STCD = T2.STCD AND (T1.TM = T2.TM)");
		INNER_JOIN("(" + slaveTableSql + ") T3 on T1.STCD=T3.STCD");
		return SQL();
	}

	private String columnToFieldMap() {
		StringBuffer sb = new StringBuffer();
		sb.append("T3.LGTD,");
		sb.append("T3.LTTD,");
		sb.append("T1.STCD,");
		sb.append("T3.STNM,");
		sb.append("T3.ADDVCD inProv,");
		sb.append("'ZG' sttp,");
		sb.append("T2.GWBD val,");
		sb.append("T3.FRGRD frgrd,");
		sb.append("T2.TM tm");
		return sb.toString();
	}

	private String primaryTableBuild1() {
		SELECT("STCD");
		SELECT("MAX(TM) AS TM");
		FROM(ST_GRW_R);
		WHERE("TM>#{startDate}");
		WHERE("TM<=#{endDate}");
		GROUP_BY("STCD");
		return SQL();
	}
	
	private String primaryTableBuild2() {
		SELECT("STCD");
		SELECT("TM");
		SELECT("GWBD");
		FROM(ST_GRW_R);
		WHERE("TM>#{startDate}");
		WHERE("TM<=#{endDate}");
		ORDER_BY("GWBD");
		return SQL();
	}

	private String slaveTableBuild() {
		SELECT("*");
		FROM(ST_STBPRP_B + " B");
		WHERE("B.STTP IN('DD','PP','RR','SS','ZQ','ZZ')");
		WHERE("B.LGTD IS NOT NULL");
		WHERE("B.LTTD IS NOT NULL");
		WHERE("B.ADDVCD IS NOT NULL");
		return SQL();
	}

	private Map<String, Date> reParams(Map<String, Date> maps) {
		Date datePoint = (Date) maps.get("datePoint");
		Date startDate = DateUtils.addDays(datePoint, -10);
		Date endDate = datePoint;

		maps.put("startDate", startDate);
		maps.put("endDate", endDate);
		maps.remove("datePoint");

		return maps;
	}
}
